<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

/**
 *
 * @author Joker
 *        
 */
class CompanyJobsType extends Model
{
    protected $table='company_jobs_type';
    
    protected $primaryKey = 'cat_id';
    
    protected $fillable = array('cat_id','name','parent_id','sort','display','is_recommond');
    
    
    /**
     * 根据子分类获取父分类
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function parentCate()
    {
        return $this->belongsTo('App\Models\CompanyJobsType', 'parent_id', 'cat_id');
    }
    
    /**
     * 获取某个父分类下的所有子分类
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function childrenCates()
    {
        return $this->hasMany('App\Models\CompanyJobsType', 'parent_id', 'cat_id');
    }

    /**
     * 获取推荐的三级子分类
     */
    public function recommondChildCates($pid)
    {
       $subCates = DB::table('company_jobs_type')->where(array('parent_id'=> $pid,'display'=>0))->lists('cat_id');
       return DB::table('company_jobs_type')->whereIn('parent_id',$subCates)
           ->where('is_recommond',1)
           ->where('display',0)
           ->orderBy('updated_at','DESC')
           ->take(10)
           ->get();
    }

    /**
     * 获取公司标签
     */
    public function comTags($comid)
    {
        $ctags = DB::table('company_tags')->where('company_id','=',$comid)->lists('tags_id');
        return DB::table('tags')->whereIn('tags_id',$ctags)
            ->orderBy('updated_at','DESC')
            ->take(3)
            ->get();
    }

    /**
     * 获取公司类型
     */
    public function comTypes($comid)
    {
        $types = DB::table('company_type_link')->where('company_id','=',$comid)->lists('type_id');
        return DB::table('company_type')->whereIn('id',$types)
            ->orderBy('id','ASC')
            ->take(2)
            ->get();
    }

    /**
     * 获取公司类型 一个类型
     */
    public function comTypeOne($comid)
    {
        $types = DB::table('company_type_link')->where('company_id','=',$comid)->lists('type_id');
        return DB::table('company_type')->whereIn('id',$types)
            ->orderBy('id','ASC')
            ->take(1)
            ->pluck('name');
    }

    /**
     * 获取发布职位数量最多的公司
     */
    public function maxCountcoms($skip,$pagesize)
    {
        return DB::table('company_jobs')
                ->select(DB::raw('count(*) as count, company_id'))
                ->whereNull('deleted_at')
                ->groupBy('company_id')
                ->orderBy('count','DESC')
                ->skip($skip)
                ->take($pagesize)
                ->get();
    }
}
